package info.scholtus.open.provider;

import static info.scholtus.open.util.LogUtils.LOGD;
import static info.scholtus.open.util.LogUtils.LOGW;
import static info.scholtus.open.util.LogUtils.makeLogTag;
import info.scholtus.open.provider.Contract.BlocksColumns;
import info.scholtus.open.provider.Contract.Categories;
import info.scholtus.open.provider.Contract.CategoriesColumns;
import info.scholtus.open.provider.Contract.PropertiesColumns;
import info.scholtus.open.provider.Contract.Sites;
import info.scholtus.open.provider.Contract.SitesColumns;
import info.scholtus.open.provider.Contract.Vendors;
import info.scholtus.open.provider.Contract.VendorsColumns;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;


public class Database extends SQLiteOpenHelper {

    private static final String TAG = makeLogTag(Database.class);

    private static final String DATABASE_NAME = "open.db";
    
    // CAUTION: Data may be erased when changed this
    private static final int VER_LAUNCH = 1;
    private static final int VER_SESSION_TYPE = 3;

    private static final int DATABASE_VERSION = VER_SESSION_TYPE;
    
    interface Tables {
    	String CATEGORIES = "categories";
    	String VENDORS = "vendors";
    	String SITES = "sites";
    	String PROPERTIES = "properties";
    	String BLOCKS = "blocks";
    }
    
    /** {@code REFERENCES} clauses. */
    private interface References {
        String CATEGORY_ID = "REFERENCES " + Tables.CATEGORIES + "(" + Categories.CATEGORY_ID + ")";
        String VENDOR_ID = "REFERENCES " + Tables.VENDORS + "(" + Vendors.VENDOR_ID + ")";
        String SITE_ID = "REFERENCES " + Tables.SITES + "(" + Sites.SITE_ID + ")";
 //       String PROPERTY_ID = "REFERENCES " + Tables.PROPERTIES + "(" + Properties.PROPERTY_ID + ")";
 //       String BLOCK_ID = "REFERENCES " + Tables.BLOCKS + "(" + Blocks.BLOCK_ID + ")";
    }
    
	public Database(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

        db.execSQL("CREATE TABLE " + Tables.CATEGORIES + " ("
                + BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + CategoriesColumns.CATEGORY_ID + " TEXT NOT NULL,"
                + CategoriesColumns.CATEGORY_NAME + " TEXT NOT NULL,"
                + CategoriesColumns.CATEGORY_ABSTRACT + " TEXT,"
                + "UNIQUE (" + CategoriesColumns.CATEGORY_ID + ") ON CONFLICT REPLACE)");

        db.execSQL("CREATE TABLE " + Tables.VENDORS + " ("
                + BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + VendorsColumns.VENDOR_ID + " TEXT NOT NULL,"
                + VendorsColumns.VENDOR_NAME + " TEXT NOT NULL,"
                + Categories.CATEGORY_ID + " TEXT " + References.CATEGORY_ID + ","
                + "UNIQUE (" + VendorsColumns.VENDOR_ID + ") ON CONFLICT REPLACE)");

        db.execSQL("CREATE TABLE " + Tables.SITES + " ("
                + BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + SitesColumns.SITE_ID + " TEXT NOT NULL,"
                + SitesColumns.SITE_NAME + " TEXT NOT NULL,"
                + SitesColumns.SITE_ABSTRACT + " TEXT,"
                + Vendors.VENDOR_ID + " TEXT " + References.VENDOR_ID + ","
                + "UNIQUE (" + SitesColumns.SITE_ID + ") ON CONFLICT REPLACE)");

        db.execSQL("CREATE TABLE " + Tables.PROPERTIES + " ("
                + BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + PropertiesColumns.PROPERTY_ID + " TEXT NOT NULL,"
                + PropertiesColumns.PROPERTY_TYPE + " TEXT NOT NULL,"
                + PropertiesColumns.PROPERTY_VALUE + " TEXT NOT NULL,"
                + Sites.SITE_ID + " TEXT " + References.SITE_ID + ","
                + "UNIQUE (" + PropertiesColumns.PROPERTY_ID + ") ON CONFLICT REPLACE)");
        
        db.execSQL("CREATE TABLE " + Tables.BLOCKS + " ("
                + BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + BlocksColumns.BLOCK_ID + " TEXT NOT NULL,"
                + BlocksColumns.BLOCK_SPAN + " TEXT NOT NULL,"
                + BlocksColumns.BLOCK_DATE_START + " TEXT,"
                + BlocksColumns.BLOCK_DATE_END + " TEXT,"
                + BlocksColumns.BLOCK_HOUR_START + " TEXT NOT NULL,"
                + BlocksColumns.BLOCK_HOUR_END + " TEXT NOT NULL,"
                + Sites.SITE_ID + " TEXT " + References.SITE_ID + ","
                + "UNIQUE (" + BlocksColumns.BLOCK_ID + ") ON CONFLICT REPLACE)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		LOGD(TAG, "onUpgrade() from " + oldVersion + " to " + newVersion);

        // NOTE: This switch statement is designed to handle cascading database
        // updates, starting at the current version and falling through to all
        // future upgrade cases. Only use "break;" when you want to drop and
        // recreate the entire database.

        int version = oldVersion;

        switch (version) {
            case VER_LAUNCH:
            	// Launch version, update table structure, for example:
                /* db.execSQL("ALTER TABLE " + Tables.SESSIONS + " ADD COLUMN "
                        + SessionsColumns.SESSION_TYPE + " TEXT");
                */
            	// If needed, add more "case" clauses as versions get launched...
        //	case VER_2_0:
            	// Done, now mark as updated so nothing gets deleted...
            	break;
            case 2:
                version = VER_SESSION_TYPE;
        }

        LOGD(TAG, "after upgrade logic, at version " + version);
        if (version != DATABASE_VERSION) {
            LOGW(TAG, "Destroying old data during upgrade");

            db.execSQL("DROP TABLE IF EXISTS " + Tables.BLOCKS);
            db.execSQL("DROP TABLE IF EXISTS " + Tables.CATEGORIES);
            db.execSQL("DROP TABLE IF EXISTS " + Tables.SITES);
            db.execSQL("DROP TABLE IF EXISTS " + Tables.PROPERTIES);
            db.execSQL("DROP TABLE IF EXISTS " + Tables.VENDORS);

            // Drop triggers (future, I know)
            // db.execSQL("DROP TRIGGER IF EXISTS " + Triggers.VENDOR_SEARCH_INSERT);

            // Drop cross tables
            // db.execSQL("DROP TABLE IF EXISTS " + Tables.VENDORS_CATEGORIES);

            onCreate(db);
        }

	}

    public static void deleteDatabase(Context context) {
        context.deleteDatabase(DATABASE_NAME);
    }

}
